﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration.Install;
using System.Linq;
using System.Data.SqlClient;
using System.Net.Configuration;
using System.Xml;
using System.Windows.Forms;
using System.Data;
using DevComponents.DotNetBar;


namespace RM2012Server.Model
{
    public partial class DataServices : System.Data.DataTable
    {
        #region Fields
        public static SqlConnection conn;
        public static String connString = "";
        public SqlCommand cmd;
        public SqlDataAdapter da;
        #endregion

        #region Constructor
        public DataServices()
        {
            InitializeComponent();
        }
        #endregion

        #region Get ConnectionString
        public static void ConnectionString()
        {
            XmlDocument doc = XML.XmlReader("Connection.xml");
            XmlElement e = doc.DocumentElement;

            try
            {
                if (e.SelectSingleNode("status").InnerText.Equals("true"))
                {
                    connString = "Data Source=" + e.SelectSingleNode("servername").InnerText + ";Initial Catalog=" + e.SelectSingleNode("database").InnerText + ";Integrated Security=True;";
                }
                else
                {
                    connString = "Data Source=" + e.SelectSingleNode("servername").InnerText + ";Initial Catalog=" + e.SelectSingleNode("database").InnerText + ";User Id=" + e.SelectSingleNode("username").InnerText + ";Password=" + e.SelectSingleNode("password").InnerText + ";";
                }
                //Luu ten database vao bo dem
                GeneralInfo.databaseName = e.SelectSingleNode("database").InnerText;
            }
            catch
            {
                MessageBox.Show("Lỗi kết nối đến cơ sở dữ liệu ! Xin vui lòng thiết lập lại kết nối...", "LỖI", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        #endregion

        #region ExecuteData
        public void Load(SqlCommand m_cmd)
        {
            cmd = m_cmd;
            try
            {
                cmd.Connection = conn;
                da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                this.Clear();
                da.Fill(this);
            }
            catch(Exception ex)
            {
                MessageBox.Show("Đã có lỗi xảy ra trong quá trình thực thi. Xin vui lòng thực hiện lại !\n" + ex.Message, "LỖI THỰC THI", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public void Load(String storedProcedureName, params SqlParameter[] arrParam)
        {
            try
            {
                cmd = new SqlCommand(storedProcedureName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                this.Clear();
                da.Fill(this);
            }
            catch(Exception ex)
            {
                MessageBox.Show("Đã có lỗi xảy ra trong quá trình thực thi. Xin vui lòng thực hiện lại !\n" + ex.Message, "LỖI THỰC THI", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public static bool OpenConnection()
        {
            if (connString == "")
            {
                ConnectionString();
            }

            try
            {
                if (conn == null)
                {
                    conn = new SqlConnection(connString);
                }
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                return true;
            }
            catch
            {
                conn.Close();
                return false;
            }
        }

        public void CloseConnection()
        {
            conn.Close();
        }

        public int ExecuteNoneQuery()
        {
            int result = 0;
            SqlTransaction tran = null;
            try
            {
                tran = conn.BeginTransaction();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = tran;
                da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                SqlCommandBuilder builder = new SqlCommandBuilder(da);
                result = da.Update(this);
                tran.Commit();
            }
            catch
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
            }
            return result;
        }

        public int ExecuteNoneQuery(String storedProcedureName, params SqlParameter[] arrParam)
        {
            int result = 0;
            SqlTransaction tran = null;
            try
            {
                tran = conn.BeginTransaction();
                cmd = new SqlCommand(storedProcedureName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = tran;
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                SqlCommandBuilder builder = new SqlCommandBuilder(da);
                result = da.Update(this);
                tran.Commit();
            }
            catch
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
            }
            return result;
        }

        public int ExecuteUpdate(String storedProcedureName, params SqlParameter[] arrParam)
        {
            int result = 0;
            SqlTransaction tran = null;
            try
            {
                tran = conn.BeginTransaction();
                cmd = new SqlCommand(storedProcedureName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = tran;
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                result = cmd.ExecuteNonQuery();
                this.AcceptChanges();
                tran.Commit();
            }
            catch
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
            }
            return result;
        }

        public object ExecuteScalar(String storedProcedureName, params SqlParameter[] arrParam)
        {
            object result = null;
            SqlTransaction tran = null;

            try
            {
                tran = conn.BeginTransaction();
                cmd = new SqlCommand(storedProcedureName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = tran;
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                result = cmd.ExecuteScalar();
                this.AcceptChanges();
                tran.Commit();
                if (result == DBNull.Value)
                {
                    return null;
                }
            }
            catch
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
            }
            return result;
        }
        #endregion
    }
}
